<?

//Initiate Authenticate
SecurityRedirect ();

//initiate Vars
define("PRIMARY_KEY","kdcust");
$KEY[]         = array("PRIMARY_KEY"=>PRIMARY_KEY);
$FileName      = "ais_transaction_cashbankreceived.php";
if (GetParam("FormName","")=="CashBankForm") {$FileTemplate  = "ais_transaction_cashbankreceived_cnlist.html";}
elseif (GetParam("FormName","")=="CNSummaryForm") {$FileTemplate  = "ais_transaction_cn_dnlist.html";}
else $FileTemplate  = "ais_transaction_cashbankreceived_memolist.html";

$curr = GetParam("curr","");

$PageSize      = 10 ;
$TableName     = "ais_piutang_remain";
$HTMLGridList  = "CustomerGrid";
//************ FORM ACTION **************
    if (GetParam("Action","")=="Delete") {
        DeleteRecord ("txtCheck",$PageSize);
    }

$FCode = GetParam("memorialCodeSearch","");
$FormName = GetParam("FormName","");
$QueryGet  = GetQueryString("All", array('PageNum'));

//Initiate Database Table For Search;
//******* DATABASE FORM QUERY ***********
$DBMysql = $DBConnection->GetConnection1();
//print "test";


GridList (GetParam("transDate",""),GetParam("office",""),GetParam("curr",""),GetParam("debtur",""));

$SQL      = "
                 SELECT $TableName.*, ais_memorial.kdact,
                 ais_piutang.nobkta,ais_piutang.rate,ais_piutang.skada, ais_memorial.dk as dk, (ifnull(jumlahcurr,0)+ifnull(jumlah30,0)+ifnull(jumlah60,0)+ifnull(jumlah60g,0)) as total_piutang, ais_memorial.jo_code
                 FROM $TableName, ais_piutang
                 LEFT JOIN ais_memorial ON ais_piutang.nobkt=ais_memorial.nobkt AND ais_memorial.urut=999
                 WHERE ais_piutang.debtur='".GetParam("debtur","")."' AND
                 $TableName.nobkt like 'ND%' AND $TableName.nobkt=ais_piutang.nobkt
                ";
if (trim($FCode))
  $SQL .= " AND ais_memorial.nobkt like '%$FCode%'";


$SQL .=" ORDER BY ".$TableName.".nobkt DESC ";
//echo $SQL;
//******* SHOW FORM CONTENT *********
//$SQL .= "GROUP BY $TableName.nobkt ";
//$SQLCount .= " GROUP BY $TableName.nobkt ";

//print $SQL;
$PageNum = 1;

if (GetParam("PageNum","")) $PageNum  = GetParam("PageNum","");

//Default value
if (!isset($_GET)) $_GET=&$HTTP_GET_VARS ;
if (isset($_GET['PageNum'])) {
  $PageNum = $_GET['PageNum'] ;
} else {
    $PageNum = 1 ;
}

//Default value
if (isset($_GET['RecCnt'])) {
  $RecCnt = intval($_GET['RecCnt']) ;
} else {
    $RecCnt = -1 ;
}

$TBS = new clsTinyButStrong ;
$TBS->LoadTemplate($MOD_TEMPLATE_DIR."/".$FileTemplate);
$TBS->MergeBlock("bln",$KEY);
$RecCnt = $TBS->MergeBlock("blg",$DBMysql,$SQL,$PageSize,$PageNum,$RecCnt);

$TBS->MergeNavigationBar('nv',array('size'=>10,'pos'=>'centred'),$PageNum,$RecCnt,$PageSize) ;
$TBS->Show();

function FlushCustomer ($TableName, $tmp) {
    global $DBConnection,$DBGeneral,$DBCoins;

    if ($tmp) $TEMPORARY = "TEMPORARY";

    $SQL = "DROP TABLE IF EXISTS `$TableName`;";
    $DBConnection->dbc->query($SQL);

    $SQL= "CREATE $TEMPORARY TABLE `$TableName` (
              `kodecustomer` varchar(20) NOT NULL default '',
              `nama` varchar(50) default NULL,
              `cr_term` int(4) default NULL
              )";
    $DBConnection->dbc->query($SQL);
  $SQL = "INSERT INTO `$TableName` SELECT CUDEBTUR_FIN,cuname,top FROM tbldebtur GROUP BY CUDEBTUR ";
  //$SQL = "INSERT INTO `$TableName` SELECT CUDEBTUR_FIN,cuname,top FROM tbldebtur GROUP BY CUDEBTUR ";
  $DBConnection->dbc->query($SQL);


    return true;
}
function GridList ($transDate,$office,$curr,$debtur) {
            global $DBConnection;
            //$TEMP = "TEMPORARY";

            FlushCustomer ("ais_customer", 0);
            //$current_date = date("Y-m-d",mktime (0,0,0,GetParam("m",""),1,GetParam("y","")));

            list($day,$month,$year) = explode("/",$transDate);
            $current_date = "$year-$month-$day";

            $SQL = "DROP TABLE IF EXISTS `ais_piutang_terhutang`";
            $DBConnection->dbc->query($SQL);

            $SQL = "
                    CREATE $TEMP TABLE `ais_piutang_terhutang` (
                      `ktrasl` varchar(5) default NULL,
                      `tglbkt` date,
                      `nobkt` varchar(16) default NULL,
                      `nobkta` varchar(16) default NULL,
                      `debtur` varchar(16) default NULL,
                      `debnam` varchar(32) default NULL,
                      `uraian` text default NULL,
                      `curr` varchar(4) default NULL,
                      `jumlah` varchar(32) default NULL,
                      `cr_term` tinyint(4)default NULL,
                      `aging` varchar(4)default NULL
                    ) TYPE=MyISAM;
                   ";
            $DBConnection->dbc->query($SQL);

            $SQL = "
                    INSERT INTO ais_piutang_terhutang
                    SELECT KTRASL, TGLBKT, NOBKT,NOBKTA, DEBTUR, DEBNAM, URAIAN, CURR, JUMLAH,  IFNULL(CR_TERM,0) AS TOP,
                                        IF('$current_date'<=ADDDATE( tglbkt,INTERVAL ais_customer.cr_term DAY ),'0',
                                        IF('$current_date'<=ADDDATE( tglbkt,INTERVAL 30+ais_customer.cr_term DAY ),'30',
                                        IF('$current_date'<=ADDDATE( tglbkt,INTERVAL 60+ais_customer.cr_term DAY ),'60','60g')))
                    FROM ais_piutang
                    LEFT JOIN ais_customer ON ais_piutang.DEBTUR=ais_customer.kodecustomer
                    WHERE srt='1' AND ais_piutang.tglbkt<='$current_date' ";

                                        if ($office) $SQL .= " AND ais_piutang.ktrasl = '".$office."' ";
                                        if ($curr)   $SQL .= " AND ais_piutang.curr = '".$curr."' ";
                                        if ($debtur) $SQL .=" AND ais_customer.kodecustomer  = '".$debtur."' ";
                                        $SQL .= " order by nobkt";

                        //print $SQL;die();

            $DBConnection->dbc->query($SQL);

            $SQL = "DROP TABLE IF EXISTS `ais_piutang_terbayar`";
            $DBConnection->dbc->query($SQL);

            $SQL = "
                    CREATE $TEMP TABLE `ais_piutang_terbayar` (
                      `nobkt` varchar(16) default NULL,
                      `nobkta` varchar(16) default NULL,
                       `bayar` varchar(32) default NULL
                    ) TYPE=MyISAM;
                   ";
            $DBConnection->dbc->query($SQL);

            $SQL = "
                    INSERT INTO ais_piutang_terbayar
                    SELECT NOBKT, NOBKTA, SUM(JUMLAH)
                    FROM ais_piutang
                    LEFT JOIN ais_customer ON ais_piutang.DEBTUR=ais_customer.kodecustomer
                    WHERE srt='2' AND ais_piutang.tglbkt<='$current_date' ";
                                        if ($office)   $SQL .= " AND ais_piutang.ktrasl = '".$office."' ";
                                        if ($curr)   $SQL .= " AND ais_piutang.curr = '".$curr."' ";
                                        if ($debtur) $SQL .=" AND ais_customer.kodecustomer  = '".$debtur."' ";

                        $SQL .= " group by nobkta order by nobkta";
                        //print $SQL;die();
            $DBConnection->dbc->query($SQL);


            $SQL = "DROP TABLE IF EXISTS `ais_piutang_remain`";
            $DBConnection->dbc->query($SQL);

            $SQL = "
                    CREATE $TEMP TABLE `ais_piutang_remain` (
                      `ktrasl` varchar(5) default NULL,
                      `tglbkt` date,
                      `nobkt` varchar(16) default NULL,
                      `debtur` varchar(16) default NULL,
                      `debnam` varchar(32) default NULL,
                      `uraian` text default NULL,
                      `curr` varchar(4) default NULL,
                       `cr_term` tinyint(4)default NULL,
                      `jumlahcurr` varchar(32)default NULL,
                      `jumlah30` varchar(32)default NULL,
                      `jumlah60` varchar(32)default NULL,
                      `jumlah60g` varchar(32)default NULL
                    ) TYPE=MyISAM;
                   ";
            $DBConnection->dbc->query($SQL);


                        $SQL = "
                                        INSERT INTO ais_piutang_remain
                                        SELECT
                                        ais_piutang_terhutang.ktrasl,
                                        ais_piutang_terhutang.tglbkt,
                                        ais_piutang_terhutang.nobkt,
                                        ais_piutang_terhutang.debtur,
                                        ais_piutang_terhutang.debnam,
                                        ais_piutang_terhutang.uraian,
                                        ais_piutang_terhutang.curr,
                                        ifnull(ais_piutang_terhutang.cr_term, 0) AS top,
                                        if(ais_piutang_terhutang.aging='0', ais_piutang_terhutang.jumlah-ifnull(ais_piutang_terbayar.bayar,0) ,0) as jumlahcurr,
                                        if(ais_piutang_terhutang.aging='30', ais_piutang_terhutang.jumlah-ifnull(ais_piutang_terbayar.bayar,0) ,0) as jumlah30,
                                        if(ais_piutang_terhutang.aging='60', ais_piutang_terhutang.jumlah-ifnull(ais_piutang_terbayar.bayar,0) ,0) as jumlah60,
                                        if(ais_piutang_terhutang.aging='60g', ais_piutang_terhutang.jumlah-ifnull(ais_piutang_terbayar.bayar,0) ,0) as jumlah90
                                        FROM
                                        ais_piutang_terhutang
                                        LEFT JOIN ais_piutang_terbayar ON (ais_piutang_terhutang.nobkt = ais_piutang_terbayar.nobkta)
                                        WHERE
                                          (ifnull(ais_piutang_terbayar.nobkt, '') = '') OR (ais_piutang_terhutang.jumlah-ais_piutang_terbayar.bayar) <> 0
                                        ORDER BY debtur,nobkt
                        ";
            $DBConnection->dbc->query($SQL);

}



?>
